<?php
    class Retreat extends CI_Model{

        public function __construct(){

            parent::__construct();
            $this->load->database();
        }

        //退宿人员查询方法
        public function retreat_select($name ,$jobNumber){

            if ($name == '' && $jobNumber == '') {
                $return = array('res' => '40002', 'msg' => '请输入必填字段' ,'data' => '');
                $result = json_encode($return);
            } else {
                $sj1 = $name == '' ? ' 1 = 1 ' : " name LIKE '%".$name."%'";
                $sj2 = $jobNumber = '' ? ' AND 1 = 1'  : " AND jobNumber LIKE '%".$jobNumber."%'";
                // $sj3 = "AND quitCheck = 'X' ";
                $sql = "SELECT name, idNumber, jobNumber, sex, department, job, area_number, floor_number, room_number, bedNumber, liveDate FROM dorm_livepeople WHERE" .$sj1 .$sj2;
                $query = $this->db->query($sql,$sj1,$sj2);
                // $RY_quit_check = 1;
                // $where = array('RY_name' => $RY_name ,'RY_quit_check' => $RY_quit_check);
                // $where = array('RY_name' => $RY_name);
                // $select = array('RY_idcardnum', 'RY_degsonid', 'RY_sex', 'RY_department', 'RY_job', 'AR_number' ,'SSL_number' ,'RY_number', 'RY_bed', 'RY_live_date' ,);
                // $this->db->select($select);
                // $this->db->where($where);
                // $query = $this->db->get('dorm_livepeople');
                if ($query->num_rows() > 0) {
                    $return_su = array('res' => '40000', 'msg' => '查询成功', 'data' => $query->result());
                    $result = json_encode($return_su);
                } else {
                    $return_fa = array('res' => '40001', 'msg' => '查询失败' ,'data' => '');
                    $result = json_encode($return_fa);
                }
            }
            return $result;
        }

        //退宿领用物品查询
        public function use_effects_select($jobNumber){
            if ($jobNumber == '') {
                $return = array('res' => '40002', 'msg' => '请输入必填字段' ,'data' => '');
                $result = json_encode($return);
            } else {
                $select = array('article_description' ,'article_number');
                $this->db->select($select);
                $this->db->where('jobNumber' ,$jobNumber);
                $query = $this->db->get('dorm_article');
                if ($query->num_rows() > 0) {
                    $return_su = array('res' => '40000', 'msg' => '查询成功', 'data' => $query->result());
                    $result = json_encode($return_su);
                } else {
                    $return_fa = array('res' => '40001', 'msg' => '查询失败' ,'data' => '');
                    $result = json_encode($return_fa);
                }
            }
            return $result;
        }

        //退宿方法
        public function retreat($name ,$jobNumber ,$check_inAddress ,$retreatDate ,$retreatType, $remarks){//$SQ_return $RY_reason
            //RY_retreat_type:1离职，2自理，3外宿
            //若必填字段未填则报错
            if ($name == '' || $jobNumber == '' || $retreatDate == '') {
                $return = array('res' => '40002', 'msg' => '请输入必填字段' ,'data' => '');
                $result = json_encode($return);
            } else {
                //退宿日期正确性校验
                //若数据大于一条则取调宿日期离现在日期最近的进行判断，若退宿日期-调宿日期<0则报错
                $query_dorm_manday_calc_se = $this->db->get_where('dorm_manday_calc' ,array('name' => $name ,'jobNumber' => $jobNumber));
                if($query_dorm_manday_calc_se->num_rows()>1){
                $sj_dorm_manday_calc_se = "name LIKE '%".$name."%' AND jobNumber LIKE '%".$jobNumber."%' ORDER BY CoordinatingDate DESC LIMIT 1";
                $sql_dorm_manday_calc_se = "SELECT * FROM dorm_manday_calc WHERE " .$sj_dorm_manday_calc_se;
                $query_dorm_manday_calc_se1 = $this->db->query($sql_dorm_manday_calc_se ,$sj_dorm_manday_calc_se);
                $start_Coordinating_date = new DateTime($query_dorm_manday_calc_se1->row()->CoordinatingDate);
                $end_Coordinating_date = new DateTime($retreatDate);
                }elseif($query_dorm_manday_calc_se->num_rows()==1){
                    //若只有一条数据则取房间入住日期进行判断，若退宿日期-房间入住日期<0报错
                    $sj_dorm_manday_calc_se = "name LIKE '%".$name."%' AND jobNumber LIKE '%".$jobNumber."%'";
                    $sql_dorm_manday_calc_se = "SELECT * FROM dorm_manday_calc WHERE " .$sj_dorm_manday_calc_se;
                    $query_dorm_manday_calc_se1 = $this->db->query($sql_dorm_manday_calc_se ,$sj_dorm_manday_calc_se);
                    $start_Coordinating_date = new DateTime($query_dorm_manday_calc_se1->row()->room_livedate);
                    $end_Coordinating_date = new DateTime($retreatDate);
                }else{;
                    //若一条数据都没有，则取人员信息的入住日期进行判断，若退宿日期-入住日期<0报错
                    $sj_dorm_manday_calc_se = "name LIKE '%".$name."%' AND jobNumber LIKE '%".$jobNumber."%'";
                    $sql_dorm_manday_calc_se = "SELECT * FROM dorm_livepeople WHERE " .$sj_dorm_manday_calc_se;
                    $query_dorm_manday_calc_se1 = $this->db->query($sql_dorm_manday_calc_se ,$sj_dorm_manday_calc_se);
                    $start_Coordinating_date = new DateTime($query_dorm_manday_calc_se1->row()->liveDate);
                    $end_Coordinating_date = new DateTime($retreatDate);
                }
                $days = $start_Coordinating_date->diff($end_Coordinating_date)->days;
                if($days<0){
                    $return = array('res' => '40001', 'msg' => '请检查退宿日期' ,'data' => '');
                    $result = json_encode($return);
                };
                 
                //在退宿记录表中查找是否有这这个人员的退宿记录，如果有则报错
                $sql = "SELECT * FROM dorm_stay_retreat_tune WHERE jobNumber LIKE '%".$jobNumber."%' AND retreatType IS NOT NULL";
                $query = $this->db->query($sql);
                if($query->num_rows()>0){
                    $return = array('res' => '40001', 'msg' => '不允许重复离职' ,'data' => '');
                    $result = json_encode($return);
                } else {
                    //取入住人员信息表中的此人的数据
                    // $where = array('name' => $name ,'jobNumber' => $jobNumber);
                    // $this->db->select('*');
                    // $this->db->where($where);
                    // $query = $this->db->get('dorm_livepeople');
                    $query = $this->db->get_where('dorm_livepeople' ,array('name' => $name, 'jobNumber' => $jobNumber));
                    //对象数组转数组
                    $query_array = (array)$query->result()[0];
                    //取当前日期
                    $date_now = date('Y-m-d');
                    //0:退宿 1:入住
                    $SQ_type = 0;
                    //取入住/退宿记录表中此人员的全部信息
                    // $this->db->select('*');
                    // $this->db->where($where_dorm_stay_retreat_tune);
                    // $query_dorm_stay_retreat_tune = $this->db->get('dorm_stay_retreat_tune');
                    $query_dorm_stay_retreat_tune = $this->db->get_where('dorm_stay_retreat_tune' ,array('name' => $name, 'jobNumber' => $jobNumber, 'type' => 0 ));
                    
                    //给要插入到入住/退宿信息表中的数据赋值
                    $data_dorm_stay_retreat_tune = array(
                        'area_number' => $query_array['area_number'],           //区域编号
                        'floor_number' => $query_array['floor_number'],         //宿舍楼编号
                        'dorm_floor' => $query_array['dorm_floor'],             //宿舍楼层号
                        'room_number' => $query_array['room_number'],           //宿舍房间号
                        'idNumber' => $query_array['idNumber'],                 //身份证号
                        'jobNumber' => $query_array['jobNumber'],               //工号
                        'name' => $query_array['name'],                         //人员姓名
                        'department' => $query_array['department'],             //部门
                        'job' => $query_array['job'],                           //岗位
                        'sex' => $query_array['sex'],                           //性别
                        'phone' => $query_array['phone'],                       //联系方式
                        'location' => $query_array['location'],                 //籍贯
                        'address' => $query_array['address'],                   //户籍地址
                        'type' => 0,                                            //类别
                        'retreatType' => $retreatType,                          //退宿类型
                        'apply_date' => $date_now,                              //申请日期
                        'bedNumber' => $query_array['bedNumber'],               //床位号
                        'wardrobe' => $query_array['wardrobe'],                 //柜子号
                        'liveDate' => $query_array['liveDate'],                 //入住日期
                        'retreatDate' => $retreatDate,                          //退宿日期
                        'emergencyContact' => $query_array['emergencyContact'], //紧急联系人
                        'relationShip' => $query_array['relationShip'],         //与紧急联系人关系
                        'emergencyPhone' => $query_array['emergencyPhone'],     //紧急联系人联系方式
                        'remarks' => $remarks,                                  //备注
                        'applyName' => $name                                    //申请人
                    );

                    //如果入住/退宿表中无此人离职记录
                    if($query_dorm_stay_retreat_tune->num_rows() == 0){
                        try{
                        //将此人员的退宿信息插入到记录表中
                        // $where1 = array('SQ_RY_name' => $RY_name ,'SQ_RY_number' => $RY_degsonid);
                        // $this->db->where($where1);
                        $this->db->insert('dorm_stay_retreat_tune', $data_dorm_stay_retreat_tune);
                        $result_dorm_stay_retreat_tune = $this->db->affected_rows();
                        }
                        catch(Exception $e){
                            echo 'Message:'.$e->getMessage();
                        }
                    }

                    //入住人员信息表更新数据
                    $data_dorm_livepeople = array(
                        'quitCheck' => 'X',                         //将是否已退宿标记为X
                        'retreatType' => $retreatType,              //退宿类型
                        'retreatDate' => $retreatDate,              //退宿日期
                        'remarks' => $remarks,                      //备注
                        'check_inAddress' => $check_inAddress,      //入住位置
                        'liveCheck' => ''                           //将是否已入住标记为空
                        );

                    //将人员信息表进行更新
                    $where = array('name' => $name ,'jobNumber' => $jobNumber);
                    $this->db->where($where);
                    $this->db->update('dorm_livepeople', $data_dorm_livepeople);
                    $result_dorm_livepeople = $this->db->affected_rows();

                    //取这个房间的实际入住人数进行更新
                    //根据人员工号取所入住房间的区域编号,宿舍楼编号,房间号
                    $select = array('area_number' ,'floor_number' ,'room_number');
                    $this->db->select($select);
                    $this->db->where('jobNumber' ,$jobNumber);
                    $query5 = $this->db->get('dorm_livepeople');

                    //对象数组转数组
                    $num = (array)$query5->result()[0];
                    // $AR_number = $num['AR_number'];
                    // $SSL_number = $num['SSL_number'];
                    // $RY_number = $num['RY_number'];
                    // $where2 = array('AR_number' => $AR_number ,'SSL_number' => $SSL_number ,'SS_number' => $RY_number);

                    //将区域编号,宿舍楼编号,房间号作为条件取房间信息表中的宿舍实际人数
                    $where2 = array('area_number' => $num['area_number'] ,'floor_number' => $num['floor_number'] ,'room_number' => $num['room_number']);
                    $select = array('peopleNumber' ,'remainNumber');
                    $this->db->select($select);
                    $this->db->where($where2);
                    $query4 = $this->db->get('dorm_roominfor');

                    //对象数组转数组
                    $peopleNumber = (array)$query4->result()[0];
                    //将宿舍实际人数-1
                    $peopleNumber_add = $peopleNumber['peopleNumber'] - 1;
                    //将宿舍剩余可住人数+1
                    $remainNumber_add = $peopleNumber['remainNumber']  + 1;
                    $data_peopleNumber = array(
                        'peopleNumber' => $peopleNumber_add,        //宿舍实际人数
                        'remainNumber' => $remainNumber_add         //宿舍剩余可住
                    );
                    //将宿舍可住人数和宿舍实际人数进行更新
                    $this->db->where($where2);
                    $this->db->update('dorm_roominfor' ,$data_peopleNumber);
                    $result_dorm_roominfor = $this->db->affected_rows();

                    //将退宿人员信息从床位表上删除
                    $data_bed = array(
                        'idNumber' => '',       //身份证号
                        'name' => '',           //姓名
                        'sex' => '',            //性别
                        'jobNumber' => '',      //工号
                        'department' => '',     //部门
                        'phone' => '',          //联系方式
                        'check_inWhether' => 0, //是否住人
                        'remarks' => ''         //备注
                    );
                    //更新床位信息表
                    $where_bed = array('name' => $name ,'jobNumber' => $jobNumber);
                    $this->db->where($where_bed);
                    $this->db->update('dorm_bed' ,$data_bed);
                    $result_bed = $this->db->affected_rows();

                    //取人天水电表条数
                    $query_manday = $this->db->get_where('dorm_manday_calc' ,array('name' => $name ,'jobNumber' => $jobNumber));
                    if($query_manday->num_rows()>1){

                        //若有多条数据取房间入住日期最近的一条将退宿日期进行更新
                        //若数据大于一条则取最近一条的调宿日期数据计算天数
                        $sj_dorm_manday_calc_se = "name LIKE '%".$name."%' AND jobNumber LIKE '%".$jobNumber."%' ORDER BY CoordinatingDate DESC LIMIT 1";
                        $sql_dorm_manday_calc_se = "SELECT * FROM dorm_manday_calc WHERE " .$sj_dorm_manday_calc_se;
                        $query_dorm_manday_calc_se1 = $this->db->query($sql_dorm_manday_calc_se ,$sj_dorm_manday_calc_se); 

                        //取最近的一条调宿日期的数据的年份
                        $year = substr($query_dorm_manday_calc_se1->row()->CoordinatingDate, 0, 4);
                        //取最近的一条调宿日期的数据的月份
                        $month = substr($query_dorm_manday_calc_se1->row()->CoordinatingDate, 5, 2);
                        //先判断最新一次的调宿日期与退宿日期是否是同年同月，若是同年同月 入住天数=退宿日期-调宿日期
                        if($year == date("Y") && $month == date("m")){
                            $start_Coordinating_date1 = new DateTime($query_dorm_manday_calc_se1->row()->CoordinatingDate);
                            $end_room_livedate = new DateTime($retreatDate);
                            $live_days = $start_Coordinating_date1->diff($end_room_livedate)->days;
                        //若非同年同月,则 入住天数=退宿日期-当月第一天
                        }else{
                            $start_Coordinating_date1 = new DateTime(date('Y-m-01'));
                            $end_room_livedate = new DateTime($retreatDate);
                            $live_days = $start_Coordinating_date1->diff($end_month)->days;
                        }
                        //将入住天数与退宿日期进行更新
                        $sql_live_days_up = "UPDATE dorm_manday_calc SET liveDays = '".$live_days."' , Check_outDate = '".$retreatDate."' WHERE name LIKE '%".$name."%' AND jobNumber LIKE '%".$jobNumber."%' AND CoordinatingDate IS NULL";
                        $query_live_days  = $this->db->query($sql_live_days_up);
                        $query_manday_calc_up = $this->db->affected_rows();
                        //更新退宿日期
                        // $sj1 = "Checkout_date = '".$Retreat_date."'";
                        // $sj2 = "WHERE RY_name LIKE '%".$RY_name."%' AND RY_degsonid LIKE '%".$RY_degsonid."%' ORDER BY Room_livedate DESC LIMIT 1";
                        // $sql = "UPDATE dorm_manday_calc SET " .$sj1 .$sj2;
                        // $query_manday_sql = $this->db->query($sql ,$sj1 ,$sj2);
                    }elseif($query_manday->num_rows()==1){
                        //若只有一条数据则将这条数据的退宿日期进行更新
                        $sj_dorm_manday_calc_se = "name LIKE '%".$name."%' AND jobNumber LIKE '%".$jobNumber."%' ";
                        $sql_dorm_manday_calc_se = "SELECT * FROM dorm_manday_calc WHERE " .$sj_dorm_manday_calc_se;
                        $query_dorm_manday_calc_se1 = $this->db->query($sql_dorm_manday_calc_se ,$sj_dorm_manday_calc_se); 
                        //先判断最新一次的调宿日期与退宿日期是否是同年同月，若是同年同月 入住天数=退宿日期-房间入住日期
                        //取房间入住日期的年份
                        $year = substr($query_dorm_manday_calc_se1->row()->room_livedate, 0, 4);
                        ///取房间入住日期的月份
                        $month = substr($query_dorm_manday_calc_se1->row()->CoordinatingDate, 5, 2);
                        //先判断房间入住日期与退宿日期是否是同年同月，若是同年同月 入住天数=退宿日期-房间入住日期
                        if($year == date("Y") && $month == date("m")){
                            $start_Coordinating_date1 = new DateTime($query_dorm_manday_calc_se1->row()->room_livedate);
                            $end_room_livedate = new DateTime($retreatDate);
                            $live_days = $start_Coordinating_date1->diff($end_room_livedate)->days;
                            //若非同年同月,则 入住天数=退宿日期-当月第一天
                            }else{
                                $start_Coordinating_date1 = new DateTime(date('Y-m-01'));
                                $end_room_livedate = new DateTime($retreatDate);
                                $live_days = $start_Coordinating_date1->diff($end_room_livedate)->days;
                            }
                        // $start_SS_livedate = new DataTime($query_manday->row()->dorm_livedate);//入住日期
                        // $end_Retreat_date =  new DataTime($retreatDate);
                        // $live_days = $start_SS_livedate->diff($end_Retreat_date)->days;
                        //更新水电人天表
                        $data_manday = array(
                            'Check_outDate' => $retreatDate,    //退宿日期
                            'liveDays' => $live_days            //入住日期
                        );
                        $where_manday = array(
                            'name' => $name,                    //人员姓名
                            'jobNumber' => $jobNumber           //工号
                        );
                        $this->db->where($where_manday);
                        $this->db->update('dorm_manday_calc' ,$data_manday);
                        $result_manday = $this->db->affected_rows();
                    }else{
                        //若一条记录都没有则在水电人天表中创建一条数据
                        //人天计算
                        //取入住日期
                        $query_liveDate = $this->db->get_where('dorm_livepeople' ,array('name' => $name, 'jobNumber' => $jobNumber));
                        //取入住日期的年份
                        $year = substr($query_liveDate->row()->liveDate, 0, 4);
                        ///取入住日期的月份
                        $month = substr($query_liveDate->row()->liveDate, 5, 2);
                        //先判断人员入住日期与退宿日期是否是同年同月，若是同年同月 入住天数=退宿日期-入住日期
                        if($year == date("Y") && $month == date("m")){
                            $start_Coordinating_date1 = new DateTime($query_liveDate->row()->liveDate);
                            $end_room_livedate = new DateTime($retreatDate);
                            $live_days = $start_Coordinating_date1->diff($end_room_livedate)->days;
                            //若非同年同月,则 入住天数=退宿日期-当月第一天
                            }else{
                                $start_Coordinating_date1 = new DateTime(date('Y-m-01'));
                                $end_room_livedate = new DateTime($retreatDate);
                                $live_days = $start_Coordinating_date1->diff($end_room_livedate)->days;
                            }
                        $data_manday_calc = array(
                            'area_number' => $query_array['area_number'],
                            'floor_number' => $query_array['floor_number'],
                            'room_number' => $query_array['room_number'],
                            'name' => $name,
                            'jobNumber' => $jobNumber,
                            'dorm_livedate' => $query_array['liveDate'],
                            'room_livedate' => $query_array['liveDate'],
                            'Check_outDate' => $retreatDate,
                            'CoordinatingDate' => '',
                            'liveDays' => $live_days
                        );
                        $this->db->insert('dorm_manday_calc' ,$data_manday_calc);
                    }
                    // print_r($result_dorm_stay_retreat_tune);
                    // print_r($result_dorm_livepeople);
                    // print_r($result_dorm_roominfor);
                    // print_r($result_bed);
                    // return;
                    //将退宿人员信息更新到人员人天信息表
                    if ($result_dorm_stay_retreat_tune && $result_dorm_livepeople && $result_dorm_roominfor && $result_bed){
                        $return_su = array('res' => '40000', 'msg' => '退宿成功', 'data' => '');
                        $result = json_encode($return_su);
                    }else{
                        $return_fa = array('res' => '40001', 'msg' => '退宿失败', 'data' => '');
                        $result = json_encode($return_fa);
                    }
                }      
            }
            return $result;
        }
}
?>